/*********************************************************************************************************************
*********   This code generates the dataset for Table 8 (Using Informed Ownership to predict ROA and SUE)    *********
*********************************************************************************************************************/

*** STEP 1: Calculate fractional ownership by informed mutual funds relative to that by all mutual funds;
*** Output: informed_own contains the informed ownership measure at the firm-fiscal year level;
proc sort data = temp.cst_annual out = unique_dates (keep=datadate) nodupkey; by datadate; 

proc sql;
  create table reg1 as
  select a.datadate, b.wficn, b.crsp_cl_grp, b.permno, b.meetingdate, b.itemonagendaid, b.issForMgmt, b.car12d_m, b.car12d_m_win,
    b.WinVote, b.margin, b.mgmt_win, b.market_value_pr2, b.market_value_adj, b.pwt, b.pwt_adj
  from unique_dates as a, test1 as b
  where 0 <= intck('month', b.meetingdate, a.datadate) <= 11; 
quit;

proc sort data = reg1; by wficn crsp_cl_grp datadate; where market_value_adj ne .;

proc means data = reg1 noprint; by wficn crsp_cl_grp datadate; 
  where car12d_m ne .; 
  var WinVote;
  output out = count_1 (drop=_type_ _freq_) n = num_votes;

proc means data = reg1 noprint; by wficn crsp_cl_grp datadate; 
  var car12d_m_win; weight market_value_adj; 
  output out = winret_1 (drop=_type_ _freq_) mean = vote_alpha;

data measure_1; merge count_1 winret_1; by wficn crsp_cl_grp datadate; 

proc sort data = temp.fundinfo nodupkey out = fundinfo; by wficn crsp_cl_grp caldt; where mtna ne .; 

proc sql;
  create table measure_1 as
  select a.*, b.caldt, b.crsp_obj_cd2, b.mtna, b.mtna_adj, b.pct_common_eq
  from measure_1 as a left join fundinfo as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.caldt, a.datadate) <= 6;
quit;

proc sort; by wficn crsp_cl_grp datadate caldt; 

data measure_1; set measure_1; by wficn crsp_cl_grp datadate caldt; 
  if last.datadate;
  if (index(crsp_obj_cd2, 'ED') = 1) and mtna >= 5 and num_votes > 10; 

proc sort; by datadate;

proc rank data = measure_1 out  = measure_1 groups = 5; by datadate;
  var vote_alpha;
  ranks q_vote_alpha;

data informed_fyear; set measure_1;
  if q_vote_alpha = 4 then informed = 1; else informed = 0;
run;

data s12_holdings; set temp.s12_holdings; * temp.s12_holdings is from 02Holdings.sas;
  rqdate = intnx('quarter', rdate, 0, 'end');
  format rqdate date9.;
  wt = wt_adj;
  keep wficn rqdate rdate permno market_value wt;

proc sort data = s12_holdings; by permno rqdate wficn rdate;

data s12_holdings; set s12_holdings; by permno rqdate wficn;
  if last.wficn;

proc sql;
  create table informed_1a as
  select a.informed, a.q_vote_alpha, a.datadate, a.wficn, a.crsp_cl_grp, b.*
  from informed_fyear as a left join s12_holdings as b
  on a.wficn = b.wficn and intck('month', b.rqdate, a.datadate) in (0,1,2);
quit; 

data holdings; set temp.holdings;
  rqdate = intnx('quarter', report_dt, 0, 'end');
  format rqdate date9.;
  keep crsp_cl_grp rqdate report_dt permno market_value wt;

proc sort data = holdings; by permno rqdate crsp_cl_grp report_dt;

data holdings; set holdings; by permno rqdate crsp_cl_grp;
  if last.crsp_cl_grp;

data measure_fyear; set informed_1a;
  if rqdate = .;
  drop rqdate rdate permno market_value wt;

proc sql;
  create table informed_1b as
  select a.informed, a.q_vote_alpha, a.datadate, a.wficn, a.crsp_cl_grp, b.*
  from measure_fyear as a left join holdings as b
  on a.crsp_cl_grp = b.crsp_cl_grp and intck('month', b.rqdate, a.datadate) in (0,1,2);
quit; 

data informed_2; set informed_1a informed_1b;
  if wt > 0;
  if datadate >= '30jun2005'd;

*** retrieve common stocks;
proc sort data=crsp.stocknames(keep=permco permno ncusip cusip ticker comnam namedt nameenddt st_date end_date shrcd exchcd where=(not missing(ncusip))) 
  out=permno nodupkey; by permno namedt nameenddt;
run;

proc sql;
  create table informed_2 as
  select a.*, b.shrcd, b.exchcd
  from informed_2 as a left join permno as b
  on a.permno = b.permno and b.namedt <= a.datadate <= b.nameenddt;
quit;

proc sort nodupkey; by permno datadate informed wficn crsp_cl_grp;

proc means data = informed_2 noprint; by permno datadate; 
  var wt; where informed = 1 and shrcd in (10, 11, 12) and exchcd in (1,2,3);
  output out = informed_2a (drop=_type_ _freq_) sum = wt_informed;

proc means data = informed_2 noprint; by permno datadate; 
  var wt; where informed = 0 and shrcd in (10, 11, 12) and exchcd in (1,2,3);
  output out = informed_2b (drop=_type_ _freq_) sum = wt_uninformed;
run;

data informed_own; merge informed_2a informed_2b; by permno datadate;
  if wt_informed = . and wt_uninformed ne . then wt_informed = 0;
  if wt_informed ne . and wt_uninformed = . then wt_uninformed = 0;
  frac_informed = wt_informed/(wt_informed+wt_uninformed);
  mfo = wt_informed+wt_uninformed;
run;


*** STEP 2: Merge with stock characteristics and SUE variables... Sort stocks in each year based on informed ownership into quintiles;
** Output: temp.informed_sue_annual contains ROA, SUE, and informed ownership at the firm-fiscal year level;
proc sql;
  create table informed_3 as
  select a.*, b.gvkey, b.fyear, b.at, b.mve_crsp, b.mb, b.leverage_b, b.capex, b.sp500, b.nanalyst, b.roa, 
    b.roa_post, b.xret1y, b.ret1y, b.ind, b.siccd, b.sich, b.ff12, b.ff30, b.naicsh, b.exchcd, b.shrcd, b.mfo_all
  from informed_own as a left join temp.cst_annual as b
  on a.permno = b.permno and a.datadate = b.datadate;
quit; 

proc sort data = informed_3 nodupkey; by gvkey fyear datadate; 

data informed_3; set informed_3; by gvkey fyear;
  if first.fyear; 

proc sort; by fyear descending mve_crsp; 

data informed_3; set informed_3; by fyear;
  if first.fyear then i = 0;
  i+1;
run;

proc sql;
  create table informed_3 as 
  select a.*, b.sue as sue_post, b.prcc_f as prc_post, b.datadate as fyenddt_post
  from informed_3 as a left join temp.sue_annual (drop=date) as b
  on a.permno = b.permno and intck('month', a.datadate, b.datadate) in (12) and b.fpi = '1'; 
quit;

proc sort nodupkey; by gvkey fyear fyenddt_post;

data informed_3; set informed_3; by gvkey fyear;
  if first.fyear;

proc sql;
  create table informed_3 as 
  select a.*, b.sue, b.prcc_f as prc, b.datadate as fyenddt_sue
  from informed_3 as a left join temp.sue_annual as b
  on a.permno = b.permno and intck('month', b.datadate, a.fyenddt_post) in (12) and b.fpi = '1';
quit;

proc sort nodupkey; by gvkey fyear fyenddt_sue;

data informed_3; set informed_3; by gvkey fyear;
   year = year(datadate);
 if last.fyear; 

proc sql;
  create table proposal_count as
  select a.permno, a.fyear, b.meetingdate, b.num_votes, b.contested20, b.iss_win, b.mgmt_win
  from informed_3 as a left join temp.stock_meetings as b
  on a.permno = b.permno and 0 <= intck('month', b.meetingdate, a.datadate) <= 11;
quit; * temp.stock_meetings is from 05Meetings_CAR.sas;

proc sort; by permno fyear meetingdate;

proc means noprint; by permno fyear;
  var num_votes contested20 mgmt_win iss_win; 
  output out = proposal_count (drop=_type_ _freq_) sum = num_votes contested20 mgmt_win iss_win;
run;

proc sql;
  create table informed_3 as 
  select a.*, b.contested20 as n_contested, b.num_votes, b.contested20/b.num_votes as frac_contested, b.mgmt_win/b.num_votes as frac_mgmt_win, b.iss_win/b.num_votes as frac_iss_win
  from informed_3 as a left join proposal_count as b
  on a.permno = b.permno and a.fyear = b.fyear;
quit;

proc sql;
  create table proposal_count as
  select a.permno, a.fyear, b.meetingdate, b.num_votes, b.contested20, b.iss_win, b.mgmt_win
  from informed_3 as a left join temp.stock_meetings as b
  on a.permno = b.permno and 1 <= intck('month', a.datadate, b.meetingdate) <= 12;
quit; 

proc sort; by permno fyear meetingdate;

proc means noprint; by permno fyear;
  var num_votes contested20 mgmt_win iss_win; 
  output out = proposal_count (drop=_type_ _freq_) sum = num_votes contested20 mgmt_win iss_win;
run;

proc sql;
  create table informed_3 as 
  select a.*, b.contested20/b.num_votes as frac_contested_post, b.mgmt_win/b.num_votes as frac_mgmt_win_post, b.iss_win/b.num_votes as frac_iss_win_post
  from informed_3 as a left join proposal_count as b
  on a.permno = b.permno and a.fyear = b.fyear;
quit;

proc sort data = informed_3 nodupkey; by permno fyear; 
  where roa ne . and roa_post ne . and mb ne . and leverage_b ne . and capex ne . and sich ne . and mfo ne . and at ne . and xret1y ne .  and num_votes > 0; 

%winsor(dsetin=informed_3, dsetout=informed_4, byvar=year, vars=roa roa_post sue sue_post
  at mb leverage_b capex mfo nanalyst xret1y mfo_all, type=winsor, pctl=.1 99.9); 

data temp.informed_sue_annual; set informed_4;
  size = log(at);
  logmb = log(mb);
  log_nanalyst = log(1+nanalyst);
  ind_fyear = ind*1000000000+fyear;
  sue = sue*100;
  sue_post = sue_post*100;
  interact = frac_informed*frac_contested;
  if frac_mgmt_win = . and num_votes ne . then frac_mgmt_win = 0;
  if frac_iss_win = . and num_votes ne . then frac_iss_win = 0;
  sic2 = int(siccd/100);
  sic3 = int(siccd/10);
  naicsh4 = int(naicsh/100);
  naicsh2 = int(naicsh/10000);

proc sort data = temp.informed_sue_annual nodupkey; by gvkey fyear;
run;



proc export data= temp.informed_sue_annual 
            outfile= "D:\Dropbox\InformedVoting\informed_sue_annual.dta" 
            dbms=stata replace;
run; 
